﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using ERP.Manager.DataProvider;
using ERP.Manager.DataEntity;
using ERP.Manager.DataHelper;
using System.Data.SqlClient;
using System.Data;
using ERP.Manager.Core;

namespace ERP.Manager.SQLServerProvider
{
    public class WorkRepository : IWorkRepository
    {

        #region 查询所有发布信息的方法
        public List<WorkInfo> getWorks()
        {
            List<WorkInfo> list = new List<WorkInfo>();
            WorkInfo work = null;
            SqlDataReader read = SQLHelper.ExecuteReader(CommandType.Text, ERPSQL.ALL_WORKINFO);
            try
            {
                while (read.Read())
                {
                    work = new WorkInfo();
                    work.workId = int.Parse(read["workId"].ToString());
                    work.title = read["title"].ToString();
                    work.workContent = read["workContent"].ToString();
                    work.userInfo.userId = read["fromUserId"].ToString();
                    work.userInfo.userName = read["username"].ToString();
                    work.toUserId = read["toUserId"].ToString();
                    work.publishDate = DateTime.Parse(read["PublishDate"].ToString());
                    work.status = int.Parse(read["Status"].ToString());
                    work.updateDate = DateTime.Parse(read["UpdateDate"].ToString());
                    list.Add(work);
                }
                read.Close();
            }
            catch (Exception)
            {
                read.Close();
            }
            finally
            {
                read.Dispose();
            }
            return list;
        }
        #endregion

        #region 根据用户编号查询当前用户接收消息信息的方法
        public List<WorkInfo> getWorksByToUserId(string UserId)
        {
            List<WorkInfo> list = new List<WorkInfo>();
            WorkInfo work = null;
            SqlDataReader read = SQLHelper.ExecuteReader(CommandType.Text, ERPSQL.GET_WORKINFO_BY_USERID,
                new SqlParameter("@touserid", UserId));
            try
            {
                while (read.Read())
                {
                    work = new WorkInfo();
                    work.workId = int.Parse(read["workId"].ToString());
                    work.title = read["title"].ToString();
                    work.workContent = read["workContent"].ToString();
                    work.userInfo.userName = read["username"].ToString();
                    work.Department.departmentName = read["departmentname"].ToString();
                    work.publishDate = DateTime.Parse(read["PublishDate"].ToString());
                    work.status = int.Parse(read["Status"].ToString());
                    work.updateDate = DateTime.Parse(read["UpdateDate"].ToString());
                    work.starttime = DateTime.Parse(read["starttime"].ToString());
                    work.endtime = DateTime.Parse(read["endtime"].ToString());
                    list.Add(work);
                }
                read.Close();
            }
            catch (Exception)
            {
                read.Close();
            }
            finally
            {
                read.Dispose();
            }
            return list;
        }
        #endregion

        #region 根据用户编号查询当前用户发布消息信息的方法
        public List<WorkInfo> getWorksByFromUserId(string fromuserid, int pagecount)
        {
            List<WorkInfo> list = new List<WorkInfo>();
            WorkInfo work = null;
            SqlDataReader read = SQLHelper.ExecuteReader(CommandType.Text, ERPSQL.GET_WORKINFO_BY_RELEASEWORK,
                new SqlParameter("@fromuserid", fromuserid),
                new SqlParameter("@pagecount", pagecount));
            try
            {
                while (read.Read())
                {
                    work = new WorkInfo();
                    work.workId = int.Parse(read["workId"].ToString());
                    work.title = read["title"].ToString();
                    work.workContent = read["workContent"].ToString();
                    work.userInfo.userName = read["username"].ToString();
                    work.Department.departmentName = read["departmentname"].ToString();
                    work.publishDate = DateTime.Parse(read["PublishDate"].ToString());
                    work.status = int.Parse(read["Status"].ToString());
                    work.updateDate = DateTime.Parse(read["UpdateDate"].ToString());
                    work.starttime = DateTime.Parse(read["starttime"].ToString());
                    work.endtime = DateTime.Parse(read["endtime"].ToString());
                    list.Add(work);
                }
                read.Close();
            }
            catch (Exception)
            {
                read.Close();
            }
            finally
            {
                read.Dispose();
            }
            return list;
        }
        #endregion

        #region 根据发布消息编号查询发布消息的详细信息
        public WorkInfo getWorkById(int workId)
        {
            WorkInfo work = null;
            SqlDataReader read = SQLHelper.ExecuteReader(CommandType.Text, ERPSQL.GET_WORKINFO_BY_WORKID, new SqlParameter("@workId", workId));
            try
            {
                while (read.Read())
                {
                    work = new WorkInfo();
                    work.workId = int.Parse(read["workId"].ToString());
                    work.title = read["title"].ToString();
                    work.workContent = read["workContent"].ToString();
                    work.userInfo.userId = read["fromUserId"].ToString();
                    work.toUserId = read["toUserId"].ToString();
                    work.publishDate = DateTime.Parse(read["PublishDate"].ToString());
                    work.status = int.Parse(read["Status"].ToString());
                    work.updateDate = DateTime.Parse(read["UpdateDate"].ToString());
                    work.accessories = read["accessories"].ToString();
                }
                read.Close();
            }
            catch (Exception)
            {
                read.Close();
            }
            finally
            {
                read.Dispose();
            }
            return work;
        }
        #endregion

        #region 添加发布消息的方法
        public int addWork(WorkInfo work)
        {

            int result = SQLHelper.ExecuteNonQuery(CommandType.Text, ERPSQL.ADD_WORKINFO,
              new SqlParameter("@title", work.title),
              new SqlParameter("@workContent", work.workContent),
              new SqlParameter("@fromUserId", work.userInfo.userId),
              new SqlParameter("@toUserId", work.toUserId),
              new SqlParameter("@publishDate", work.publishDate),
              new SqlParameter("@status", work.status),
              new SqlParameter("@updateDate", work.updateDate),
              new SqlParameter("@starttime", work.starttime),
              new SqlParameter("@endtime", work.endtime),
              new SqlParameter("@accessories", work.accessories));
            return result;
        }
        #endregion

        #region 修改发布消息的方法
        public int updateWork(WorkInfo work)
        {
            int rowcount = SQLHelper.ExecuteNonQuery(CommandType.Text, ERPSQL.UPDATE_WORK,
              new SqlParameter("@title", work.title),
              new SqlParameter("@workContent", work.workContent),
              new SqlParameter("@toUserId", work.toUserId),
              new SqlParameter("@starttime", work.starttime),
              new SqlParameter("@endtime", work.endtime),
              new SqlParameter("@workId", work.workId));
            return rowcount;
        }
        #endregion

        #region 删除发布信息的方法
        public int deleteWorkById(int workId)
        {
            int result = SQLHelper.ExecuteNonQuery(CommandType.Text, ERPSQL.DELETE_WORK, new SqlParameter("@workId", workId));
            return result;
        }
        #endregion

        #region 查询正在进行工作的信息的方法
        public List<WorkInfo> allworkinfojinxing(string userid, int pagecount)
        {
            List<WorkInfo> list = new List<WorkInfo>();
            WorkInfo work = null;
            SqlDataReader read = SQLHelper.ExecuteReader(CommandType.Text, ERPSQL.ALL_WORKINFO_JINXING,
                new SqlParameter("@touserid", userid),
                new SqlParameter("@pagecount", pagecount));
            try
            {
                while (read.Read())
                {
                    work = new WorkInfo();
                    work.workId = int.Parse(read["workId"].ToString());
                    work.title = read["title"].ToString();
                    work.workContent = read["workContent"].ToString();
                    work.userInfo.userName = read["username"].ToString();
                    work.Department.departmentName = read["departmentname"].ToString();
                    work.publishDate = DateTime.Parse(read["PublishDate"].ToString());
                    work.status = int.Parse(read["Status"].ToString());
                    work.updateDate = DateTime.Parse(read["UpdateDate"].ToString());
                    work.starttime = DateTime.Parse(read["starttime"].ToString());
                    work.endtime = DateTime.Parse(read["endtime"].ToString());
                    list.Add(work);
                }
                read.Close();
            }
            catch (Exception)
            {
                read.Close();
            }
            finally
            {
                read.Dispose();
            }
            return list;
        }
        #endregion

        #region 查询已完成工作任务的信息
        public List<WorkInfo> allworkinfowancheng(string userid, int pagecount)
        {
            List<WorkInfo> list = new List<WorkInfo>();
            WorkInfo work = null;
            SqlDataReader read = SQLHelper.ExecuteReader(CommandType.Text, ERPSQL.ALL_WORKINFO_WANCHENG,
                new SqlParameter("@touserid", userid),
                new SqlParameter("@pagecount", pagecount));
            try
            {
                while (read.Read())
                {
                    work = new WorkInfo();
                    work.workId = int.Parse(read["workId"].ToString());
                    work.title = read["title"].ToString();
                    work.workContent = read["workContent"].ToString();
                    work.userInfo.userName = read["username"].ToString();
                    work.Department.departmentName = read["departmentname"].ToString();
                    work.publishDate = DateTime.Parse(read["PublishDate"].ToString());
                    work.status = int.Parse(read["Status"].ToString());
                    work.updateDate = DateTime.Parse(read["UpdateDate"].ToString());
                    work.starttime = DateTime.Parse(read["starttime"].ToString());
                    work.endtime = DateTime.Parse(read["endtime"].ToString());
                    list.Add(work);
                }
                read.Close();
            }
            catch (Exception)
            {
                read.Close();
            }
            finally
            {
                read.Dispose();
            }
            return list;
        }
        #endregion

        #region 查询已超期工作任务的信息
        public List<WorkInfo> allworkinfochaoqi(string userid, int pagecount)
        {
            List<WorkInfo> list = new List<WorkInfo>();
            WorkInfo work = null;
            SqlDataReader read = SQLHelper.ExecuteReader(CommandType.Text, ERPSQL.ALL_WORKINFO_CHAOQI,
                new SqlParameter("@touserid", userid),
                new SqlParameter("@pagecount", pagecount));
            try
            {
                while (read.Read())
                {
                    work = new WorkInfo();
                    work.workId = int.Parse(read["workId"].ToString());
                    work.title = read["title"].ToString();
                    work.workContent = read["workContent"].ToString();
                    work.userInfo.userName = read["username"].ToString();
                    work.Department.departmentName = read["departmentname"].ToString();
                    work.publishDate = DateTime.Parse(read["PublishDate"].ToString());
                    work.status = int.Parse(read["Status"].ToString());
                    work.updateDate = DateTime.Parse(read["UpdateDate"].ToString());
                    work.starttime = DateTime.Parse(read["starttime"].ToString());
                    work.endtime = DateTime.Parse(read["endtime"].ToString());
                    list.Add(work);
                }
                read.Close();
            }
            catch (Exception)
            {
                read.Close();
            }
            finally
            {
                read.Dispose();
            }
            return list;
        }
        #endregion

        #region 查询自己当前接收任务的详细信息的方法
        public WorkInfo getworkinfobyuseriddetail(string userid, int workid)
        {
            WorkInfo work = null;
            SqlDataReader read = SQLHelper.ExecuteReader(CommandType.Text, ERPSQL.GET_WORKINFO_BY_USERID_DETAIL,
                new SqlParameter("@touserid", userid),
                new SqlParameter("@workid", workid));
            try
            {
                if (read.Read())
                {
                    work = new WorkInfo();
                    work.workId = int.Parse(read["workId"].ToString());
                    work.title = read["title"].ToString();
                    work.workContent = read["workContent"].ToString();
                    work.userInfo.userName = read["username"].ToString();
                    work.Department.departmentName = read["departmentname"].ToString();
                    work.publishDate = DateTime.Parse(read["PublishDate"].ToString());
                    work.status = int.Parse(read["Status"].ToString());
                    work.updateDate = DateTime.Parse(read["UpdateDate"].ToString());
                    work.starttime = DateTime.Parse(read["starttime"].ToString());
                    work.endtime = DateTime.Parse(read["endtime"].ToString());
                    work.workinfoNote = read["workinfonote"].ToString();
                }
                read.Close();
            }
            catch (Exception)
            {
                read.Close();
            }
            finally
            {
                read.Dispose();
            }
            return work;
        }
        #endregion

        #region 修改状态值为5或者结束时间小于当前时间的方法
        public int updateworkinfostatus(string userid)
        {
            int rowcunt = SQLHelper.ExecuteNonQuery(CommandType.Text, ERPSQL.UPDATE_WORKINFO_STATUS, new SqlParameter("@touserid", userid));
            return rowcunt;
        }
        #endregion

        #region 修改工作任务的状态的方法
        public int updateworkstatus(WorkInfo workinfo)
        {
            int rowcunt = SQLHelper.ExecuteNonQuery(CommandType.Text, ERPSQL.UPDATE_WORK_STATUS,
                new SqlParameter("@status", workinfo.status),
                new SqlParameter("@workid", workinfo.workId),
                new SqlParameter("@updatedate", workinfo.updateDate),
                 new SqlParameter("@workinfonote", ""));
            return rowcunt;
        }
        #endregion

        #region 进行的工作修改为已完成然后添加完成任务的备注信息
        public int updateworkstatusandworkinfonote(WorkInfo work)
        {
            int rowcunt = SQLHelper.ExecuteNonQuery(CommandType.Text, ERPSQL.UPDATE_WORK_STATUS,
                new SqlParameter("@status", work.status),
                new SqlParameter("@workid", work.workId),
                new SqlParameter("@updatedate", work.updateDate),
                new SqlParameter("@workinfonote", work.workinfoNote));
            return rowcunt;
        }
        #endregion        

        #region 查询自己当前发布任务的详细信息的方法
        public WorkInfo getworkinfobyreleaseworkdetail(string userid, int workid)
        {
            WorkInfo work = null;
            SqlDataReader read = SQLHelper.ExecuteReader(CommandType.Text, ERPSQL.GET_WORKINFO_BY_RELEASEWORK_DETAIL,
                new SqlParameter("@fromuserid", userid),
                new SqlParameter("@workid", workid));
            try
            {
                if (read.Read())
                {
                    work = new WorkInfo();
                    work.workId = int.Parse(read["workId"].ToString());
                    work.title = read["title"].ToString();
                    work.workContent = read["workContent"].ToString();
                    work.userInfo.userId = read["userid"].ToString();
                    work.userInfo.userName = read["username"].ToString();
                    work.Department.departmentId = read["departmentid"].ToString();
                    work.Department.departmentName = read["departmentname"].ToString();
                    work.publishDate = DateTime.Parse(read["PublishDate"].ToString());
                    work.status = int.Parse(read["Status"].ToString());
                    work.updateDate = DateTime.Parse(read["UpdateDate"].ToString());
                    work.starttime = DateTime.Parse(read["starttime"].ToString());
                    work.endtime = DateTime.Parse(read["endtime"].ToString());
                }
                read.Close();
            }
            catch (Exception)
            {
                read.Close();
            }
            finally
            {
                read.Dispose();
            }
            return work;
        }
        #endregion

        #region 分页显示自己当前接收的任务的方法
        public List<WorkInfo> getworkinfobyuseridpagecount(string userid, int pagecount)
        {
            List<WorkInfo> list = new List<WorkInfo>();
            WorkInfo work = null;
            SqlDataReader read = SQLHelper.ExecuteReader(CommandType.Text, ERPSQL.GET_WORKINFO_BY_USERID_PAGECOUNT,
                new SqlParameter("@touserid", userid),
                new SqlParameter("@pagecount", pagecount));
            try
            {
                while (read.Read())
                {
                    work = new WorkInfo();
                    work.workId = int.Parse(read["workId"].ToString());
                    work.title = read["title"].ToString();
                    work.workContent = read["workContent"].ToString();
                    work.userInfo.userName = read["username"].ToString();
                    work.Department.departmentName = read["departmentname"].ToString();
                    work.publishDate = DateTime.Parse(read["PublishDate"].ToString());
                    work.status = int.Parse(read["Status"].ToString());
                    work.updateDate = DateTime.Parse(read["UpdateDate"].ToString());
                    work.starttime = DateTime.Parse(read["starttime"].ToString());
                    work.endtime = DateTime.Parse(read["endtime"].ToString());
                    list.Add(work);
                }
                read.Close();
            }
            catch (Exception)
            {
                read.Close();
            }
            finally
            {
                read.Dispose();
            }
            return list;
        }
        #endregion

        #region 查询自己当前发布的任务所有的条数记录
        public int getworkinfobyreleaseworkcount(string fromuserid)
        {
            var result = SQLHelper.ExecuteScalar(CommandType.Text, ERPSQL.GET_WORKINFO_BY_RELEASEWORK_COUNT,
                new SqlParameter("@fromuserid", fromuserid));
            return int.Parse(result.ToString());
        }
        #endregion

        #region 查询正在进行工作记录的总条数
        public int allworkifojinxingcount(string touserid)
        {
            var result = SQLHelper.ExecuteScalar(CommandType.Text, ERPSQL.ALL_WORKINFO_JINXING_COUNT,
                new SqlParameter("@touserid", touserid));
            return int.Parse(result.ToString());
        }
        #endregion

        #region 查询已完成工作任务的信息的总条数的方法
        public int allworkinfowanchengcount(string touserid)
        {
            var result = SQLHelper.ExecuteScalar(CommandType.Text, ERPSQL.ALL_WORKINFO_WANCHENG_COUNT,
                 new SqlParameter("@touserid", touserid));
            return int.Parse(result.ToString());
        }
        #endregion

        #region 查询已超期工作任务的信息的总条数的方法
        public int allworkinfochaoqicount(string touserid)
        {
            var result = SQLHelper.ExecuteScalar(CommandType.Text, ERPSQL.ALL_WORKINFO_CHAOQI_COUNT,
               new SqlParameter("@touserid", touserid));
            return int.Parse(result.ToString());
        }
        #endregion

        #region 根据用户编号查询发布信息
        public List<WorkInfo> getallworkfromuserid(string fromuserid)
        {
            List<WorkInfo> list = new List<WorkInfo>();
            WorkInfo work = null;
            SqlDataReader read = SQLHelper.ExecuteReader(CommandType.Text, ERPSQL.GETALL_WORK_FROMUSERID,
                new SqlParameter("@fromuserid", fromuserid));
            try
            {
                while (read.Read())
                {
                    work = new WorkInfo();
                    work.workId = int.Parse(read["workId"].ToString());
                    work.title = read["title"].ToString();
                    work.workContent = read["workContent"].ToString();
                    work.userInfo.userId = read["fromuserid"].ToString();
                    work.toUserId = read["touserid"].ToString();
                    work.publishDate = DateTime.Parse(read["PublishDate"].ToString());
                    work.status = int.Parse(read["Status"].ToString());
                    work.updateDate = DateTime.Parse(read["UpdateDate"].ToString());
                    work.starttime = DateTime.Parse(read["starttime"].ToString());
                    work.endtime = DateTime.Parse(read["endtime"].ToString());
                    list.Add(work);
                }
                read.Close();
            }
            catch (Exception)
            {
                read.Close();
            }
            finally
            {
                read.Dispose();
            }
            return list;
        }
        #endregion

        #region 根据用户编号删除发布信息
        public int deleteworkfromuserid(string fromuserid)
        {
            int rowcunt = SQLHelper.ExecuteNonQuery(CommandType.Text, ERPSQL.DELETE_WORK_FROMUSERID, new SqlParameter("@fromuserid", fromuserid));
            return rowcunt;
        }
        #endregion
    }
}
